#!/bin/sh
#
# OracleAppMount_tstamp.sh: shell script to automate cloning and bring up an Oracle database using backup image copies
#
#usage: OracleAppMount_tstamp.sh <Old database sid name> <New database sid name> <Oracle Home path> <Backup location mount path> <timestamp yyyy-mm-dd..hh24:mi:ss>"
osid=$1
nsid=$2
oracleHome=$3
backupLocation=$4
untiltime=$5
archmnt=$backupLocation/archivelog
dataDir=$backupLocation

BASEDIR=$(dirname $0)
#echo $BASEDIR
cd $BASEDIR

scriptsRoot=${LB_ROOT}
if [ -z "$scriptsRoot" ];then
    echo "missing env LB_ROOT"
    exit 1
fi

. ${scriptsRoot}/oracle/oracleclone/OracleAppMountComFunc.sh

recoverParams=${LB_PARAM}
if [ -n "$recoverParams" ];then
    . ${recoverParams}
    echo "source ${recoverParams}"
fi

if [ "$#" -ne 5 ]; then
echo "This script needs 5 input parameter"
echo "usage: OracleAppMount_tstamp.sh <Old database sid name> <New database sid name> <Oracle Home path> <Backup location mount path> <timestamp yyyy-mm-dd..hh24:mi:ss>"
echo " For example: OracleAppMount_tstamp.sh clonedb /home/oracle/app/oracle/product/11.2.0/dbhome_1 /act/mnt/Job_0060901_mountpoint_1341234106825 /act/mnt/vdbxlog 201404021435"
exit 1
fi

#logger for error messages
errMsgFile=$nsid"_errMsg"

function apmlogger {
     errmsg="$1"
     if [ ! -z "$errmsg" ]; then
        echo "$errmsg" > $errMsgFile
     fi
}

function apmappend {
     errmsg="$1"
     echo " $errmsg" >> $errMsgFile
}

function geterrmsg {
     ecode=$1
     if [ -z "$ecode" ]; then
        echo "missing error code when retrieving error message"
     fi
     msgrepo=$${scriptsRoot}/oracle/apmerrmsg.sh
     msg=`grep $ecode"=" $msgrepo | awk -F'=' '{print $NF}'`
     if [ ! -z "$msg" ]; then
        echo "$msg"
     else
        echo "failed to retrieve error message for $ecode"
     fi
}

function logerrormsg {
     ecode=$1
     appender=$2

     emsg=`geterrmsg $ecode`
     apmlogger "$emsg"
     if [ ! -z "$$appender" ]; then
        apmappend $appender
     fi
     echo `$tdate`$nsid": $emsg $appender"
}
#logger

if [ ! -z "$isrestore" ] && [ "$isrestore" = "true" ]; then
ORACLE_HOME=$oracleHome
export ORACLE_HOME
PATH=$ORAHOME/bin:$PATH
export PATH
ORACLE_SID=$nsid
export ORACLE_SID
TNS_ADMIN=$tnsadmindir
export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
set heading off
set feedback off
startup mount;
alter database end backup;
exit;
EOF

catalogstr=`generate_catalog_str $archmnt`
echo "catelog => $catalogstr"
export NLS_DATE_FORMAT="yyyy-mm-dd..hh24:mi:ss"
rmancmd="run
        {
        allocate channel d1 device type disk;
        allocate channel d2 device type disk;
        $catalogstr
        recover database until time \"to_date('$untiltime','yyyy-mm-dd..hh24:mi:ss')\";
        release channel d1;
        release channel d2;
        }"

dbrecover="${scriptsRoot}/oracle/oracleclone/dbrecover.txt"
if [ ! -f "$dbrecover" ]; then
   touch $dbrecover
#   chmod 777 $dbrecover
fi
ORACLE_HOME=$oracleHome
export ORACLE_HOME
PATH=$ORAHOME/bin:$PATH
export PATH
ORACLE_SID=$nsid
export ORACLE_SID
TNS_ADMIN=$tnsadmindir
export TNS_ADMIN
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

retv1=`$ORACLE_HOME/bin/rman target / log=$dbrecover append << EOF
                 $rmancmd
                 exit
EOF`

if [ $? -ne 0 ]; then
   logerrormsg EM_DB_RECOVERY
   exit 1
fi

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
set heading off
set feedback off
alter database open resetlogs;
exit;
EOF
if [ $? -ne 0 ]; then
   logerrormsg EM_DB_OPEN
   exit 1
fi

exit 0
#inband restore
fi

# correctly set the ORACLE_BASE AND ORACLE_HOME  for your environment
export ORACLE_HOME=$oracleHome
export ORACLE_BASE=$dataDir
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

if [ ! -d $ORACLE_HOME ]; then
   logerrormsg EM_ORACLE_HOME
   exit 1
fi

if [ ! -d  $ORACLE_BASE ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION
   exit 1
fi

if [ -z $archmnt ]; then
   
   echo "missing log mount path"
   exit 1
fi

Sqlplus=$ORACLE_HOME/bin/sqlplus

## psid=`get_teardown_sid $dataDir`
remountflag=$isremount

if [ $remountflag = "true" ]; then
    ## echo "Remount $psid"
    $${scriptsRoot}/oracle/oracleclone/OracleAppRemount.sh $nsid $ORACLE_HOME $dataDir
    if [ $? != 0 ]; then
       ## logerrormsg EM_REMOUNT "$psid to $nsid"
       exit 1
    fi
else
# get the last archivelog name with path
mname=$backupLocation
echo $mname

# get the production sid name from mount
if [ ! -z "$ischild" ] && [ "$ischild" = "true" ]; then
   #check prev appAware mount
   pmpdir=$dataDir"/pmpkeep"
   if [ -e "$pmpdir" ]; then
      # if prevsid is set, use it, this is the last appAware mount SID, if not the last one should the latest SID
      if [ -z "$prevsid" ]; then
         getpname=`ls -ltr $dataDir/init*.ora | tail -1 | awk '{print $NF}'`
      else
         getpname=`ls -ltr $dataDir/init$prevsid.ora | awk '{print $NF}'`
      fi
      pname=$(basename $getpname)
      echo $pname
      pname=`echo $pname | cut -d"." -f1`
      # osid=`echo $pname | cut -c5-`
   else
      getpname=`ls -ltr $mname | grep '__backup.ora'`
      echo $getpname
      pname=`echo $getpname | awk '{print $NF}'`
      echo $pname
      # osid=`echo $pname | awk -F"__" '{print $(NF-1)}'`
   fi
else
   getpname=`ls -ltr $mname | grep '__backup.ora'`
   echo $getpname
   pname=`echo $getpname | awk '{print $NF}'`
   echo $pname
   # osid=`echo $pname | awk -F"__" '{print $(NF-1)}'`
fi


echo $osid
osid1=$osid"1"
osid2=$osid"2"
osid3=$osid"3"
osid4=$osid"4"
osid5=$osid"5"
osid6=$osid"6"
echo $osid1
echo $osid2

if [ ! -z "$ischild" ] && [ "$ischild" = "true" ]; then
   prevorafile=$mname/"init"$osid".ora"
   if [ -e $prevorafile ]; then
      cp $prevorafile $mname/$osid"__backup.ora"
   fi   
fi

temporafile=$mname/$osid"__backup_temp.ora"
orafile=$mname/$osid"__backup.ora"
cp $orafile $orafile".orig"
echo $orafile
backupdir=$mname/datafile
echo $backupdir

dbfsPath=$mname/$osid"_datafiles_readonly_catalog.conf"
echo "read only tablespace file path : $dbfsPath"

# keep mountpoint with the mount for remount
echo "$backupLocation" > $backupLocation/pmpkeep

openDatabase="openDatabase_"$nsid".sql"
recoverDatabase="recoverDatabase_"$nsid".sql"
verifyDatabase="verifyDatabase.sql"
CLONE_CRT_SQL="Clone_createCtrlFile_"$nsid"_g.sql"
openDatabase1="openDatabase1_"$nsid".sql"
openDBlog="openDBlog_"$nsid".txt"

openPDatabase="openPDatabase_"$nsid".sql"
openTmpDatabase="openTmpDatabase_"$nsid".sql"
openTmpDatabase1="openTmpDatabase1_"$nsid".sql"
addUnnamed="addUnnamed_"$nsid".sql"
tabfilelist="tabfilelist_"$nsid
undofilelist="undofilelist_"$nsid".sql"
offlinefilelist="offlinefilelist_"$nsid
pluggablelist="pluggablelist_"$nsid
pglist="pglist_"$nsid
createRedolog="createRedolog_"$nsid".sql"
CLONE_CRT_SQL="Clone_createCtrlFile_"$nsid"_g.sql"
runnidsql="runnidrotbs"_$nsid".sql"

#sqlplus=$ORACLE_HOME/bin/sqlplus

if [ ! -d $mname ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION
   exit 1
fi

if [ -z "$nsid" ]; then
   logerrormsg EM_NEW_SID
   exit 1
fi

if [ -z "$orafile" ]; then
   logerrormsg EM_INIT_FILE
   exit 1
fi

if [ -z "$backupdir" ]; then
   logerrormsg EM_FIND_BACKUP_LOCATION
   exit 1
fi

oobase=$ORACLE_BASE 
# replace backed up SID with new SID
#sed -i "s/$osid/$nsid/g" $orafile
sed "/$osid2/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid3/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid4/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid5/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/$osid6/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/__/d" $orafile > $temporafile
mv $temporafile $orafile

sed "s/'$osid'/'$nsid'/g" $orafile > $temporafile
mv $temporafile $orafile

# make required directory structures
mkdir -p $ORACLE_BASE/oradata/$nsid
mkdir -p $ORACLE_BASE/flash_recovery_area/$nsid
mkdir -p $ORACLE_BASE/admin/$nsid/adump
mkdir -p $ORACLE_BASE/admin/$nsid/dpdump
mkdir -p $ORACLE_BASE/admin/$nsid/control
mkdir -p $ORACLE_BASE/admin/$nsid/archivelog

controlfile="*.control_files='$ORACLE_BASE/admin/$nsid/control/control01.ctl','$ORACLE_BASE/admin/$nsid/control/control02.ctl'"
dbcreatefile="*.db_create_file_dest='$ORACLE_BASE/oradata/$nsid'"
dbrecoveryfile="*.db_recovery_file_dest='$ORACLE_BASE/flash_recovery_area/$nsid'"
if [ -z "$diagnostic_dest" ]; then
  mkdir -p $ORACLE_HOME/../../admin/$nsid/adump
  mkdir -p $ORACLE_HOME/../../diag
else
  mkdir -p $diagnostic_dest/$nsid
  mkdir -p $diagnostic_dest/$nsid/admin/adump
fi
logarchivedest="*.log_archive_dest_1='LOCATION=$ORACLE_BASE/admin/$nsid/archivelog/'"

nsidlen=`echo -n $nsid | wc -m`
if [ $nsidlen -gt 8 ]; then
newdb_name=`echo $nsid | cut -c1-8`
db_unique_name=$nsid
else
newdb_name=$nsid
fi

dbname="*.db_name='$newdb_name'"
dbuniquename="*.db_unique_name='$nsid'"

jqprocess="*.job_queue_processes=0"

if [ -n "$totalmemory" ]; then
    echo "memory_target setup will be used"
    sed '/sga_max_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/sga_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/pga_aggregate_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/memory_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/memory_max_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/streams_pool_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/db_cache_size/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/shared_pool_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/db_keep_cache_size/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/shared_pool_reserved_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/large_pool_size/d' $orafile > $temporafile
    mv $temporafile $orafile
    totalmemoryM=$totalmemory"m"
    mtarget="*.memory_target=$totalmemoryM"
    echo "$mtarget" >> $orafile
elif [ -n "$sgc" ] && [ -n "$pgc" ];then
    echo " total memory and SGA is defined "
    echo "sga and pga setup will be used"
    sed '/sga_max_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/sga_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/pga_aggregate_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/memory_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/memory_max_target=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/streams_pool_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/db_cache_size/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/shared_pool_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/db_keep_cache_size/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/shared_pool_reserved_size=/d' $orafile > $temporafile
    mv $temporafile $orafile
    sed '/large_pool_size/d' $orafile > $temporafile
    mv $temporafile $orafile
    sgam=$sgc"m"
    echo "oracle sga=$sgam"
    sgatarget="*.sga_target=$sgam"
    pgam=$pgc"m"
    echo "oracle pga=$pgam"
    pgatarget="*.pga_aggregate_target=$pgam"
    echo "memory_target=0" >> $orafile
    echo "$sgatarget" >> $orafile
    echo "$pgatarget" >> $orafile

    lps=$((($sgc*10)/100))
    if [ "$lps" -gt "200" ]; then
       lapsm=$lps"m"
    else
       lapsm=200m
    fi

    largepool="*.large_pool_size=$lapsm"
    echo "$largepool" >> $orafile

    if [ ! -z "$shared_pool_size" ]; then
        sps=$shared_pool_size"m"
        sharedpoolsize="*.shared_pool_size=$sps"
        echo "$sharedpoolsize" >> $orafile
    fi

    if [ ! -z "$db_cache_size" ]; then
       dcs=$db_cache_size"m"
       dbcachesize="*.db_cache_size=$dcs"
       echo "$dbcachesize" >> $orafile
    fi
else
  echo " SGA is not defined "
  echo "memory_target is not defined"
fi

sed '/db_recovery_file_dest_size=/d' $orafile > $temporafile
mv $temporafile $orafile
if [ ! -z "$db_recovery_file_dest_size" ]; then
drfds=$db_recovery_file_dest_size"m"
dbrecoveryfilesize="*.db_recovery_file_dest_size=$drfds"
else
dbrecoveryfilesize="*.db_recovery_file_dest_size=50g"
fi

sed '/processes=/d' $orafile > $temporafile
mv $temporafile $orafile
if [ -n "$processes" ]; then
  process="*.processes=$processes"
  echo "oracle processes=$processes"
else
  process="*.processes=500"
fi

if [ ! -z "$open_cursors" ]; then
sed "/open_cursors=/d" $orafile > $temporafile
mv $temporafile $orafile
opencursors="*.open_cursors=$open_cursors"
echo "$opencursors" >> $orafile
fi

sed "/control_files=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/audit_file_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_create_file_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_recovery_file_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/diagnostic_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/log_archive_dest/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/LOG_ARCHIVE_DEST/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/cluster_database=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/remote_listener=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/background_dump_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/core_dump_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/user_dump_dest=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/local_listener=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_name=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/dispatchers=/d" $orafile > $temporafile
mv $temporafile $orafile

sed "/instance_number=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/thread=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_unique_name=/d" $orafile > $temporafile
mv $temporafile $orafile

sed "/resource_manager_plan/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/thread=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/THREAD=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/instance_number=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/undo_tablespace=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/__/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/service_names=/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/db_create_online_log_dest/d" $orafile > $temporafile
mv $temporafile $orafile
sed "/SPFILE=/d" $orafile > $temporafile
mv $temporafile $orafile

sed  "/dg_broker_config_file/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/dg_broker_start/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/fal_client/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/fal_server/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_config/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_trace/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_min_succeed_dest/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_file_name_convert/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/standby_file_management/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/db_file_name_convert/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/archive_lag_target/d" $orafile > $temporafile
mv $temporafile $orafile
sed  "/log_archive_trace/d" $orafile > $temporafile
mv $temporafile $orafile

sed  "/cluster_interconnects/d" $orafile > $temporafile
mv $temporafile $orafile
sed '/_diag_adr_trace_dest/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/cluster_database_instances/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/instance_name/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/standby_archive_dest/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/background_dump_dest/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/_fix_control/d' $orafile > $temporafile
mv $temporafile $orafile
sed '/event/d' $orafile > $temporafile
mv $temporafile $orafile

sed '/use_large_pages/d' $orafile > $temporafile
mv $temporafile $orafile

echo "$dbname" >> $orafile
echo "$dbuniquename" >> $orafile
echo "$controlfile" >> $orafile
echo "$dbcreatefile" >> $orafile
echo "$dbrecoveryfile" >> $orafile
echo "$dbrecoveryfilesize" >> $orafile
if [ ! -z "$diagnostic_dest" ]; then
 dbdiagnostic="*.diagnostic_dest='$diagnostic_dest'"
 echo "$dbdiagnostic" >> $orafile
 adest="*.audit_file_dest='$diagnostic_dest/$nsid/admin/adump'"
 echo "$adest" >> $orafile
else
 auditfile="*.audit_file_dest='$ORACLE_BASE/admin/$nsid/adump'"
 echo "$auditfile" >> $orafile
fi
#echo "$dbdiagnostic" >> $orafile
echo "$logarchivedest" >> $orafile
echo "$process" >> $orafile
echo "$jqprocess" >> $orafile

sed '/disk_asynch_io/d' $orafile > $temporafile
mv $temporafile $orafile
diskasyncio="*.disk_asynch_io='TRUE'"
echo "$diskasyncio" >> $orafile

# generate a controlfile creation script
./Clone_generateCtrlFileScript.sh $nsid $backupdir $ORACLE_BASE"/oradata"

if [ $? -ne 0 ]; then
   logerrormsg EM_GENERATE_CONTROLFILE
   exit 1
fi

#create sql file to open the database and add tempfile
if [ -e $openDatabase ]; then
   echo "removing $openDatabase before generating a new one"
   rm -f $openDatabase
fi

if [ -e $recoverDatabase ]; then
   echo "removing $recoverDatabase before generating a new one"
   rm -f $recoverDatabase
fi

if [ -e $openDatabase1 ]; then
   echo "removing $openDatabase1 "
   rm -f $openDatabase1
fi
if [ -e $addUnnamed ]; then
   echo "removing $addUnnamed "
   rm -f $addUnnamed
fi

if [ -e $tabfilelist ]; then
   #echo "removing $tabfilelist before generating a new one"
   rm -f $tabfilelist
fi

if [ -e $undofilelist ]; then
  # echo "removing $undofilelist "
   rm -f $undofilelist
fi

if [ -e $openDBlog ]; then
  # echo "removing $openDBlog "
   rm -f $openDBlog
fi

# now export new SID, do this outside of shell, before invoke this shell
export ORACLE_SID=$nsid

# copy ora file
#cp $orafile $ORACLE_HOME/dbs/init"$nsid".ora
initf=$ORACLE_HOME/dbs/init"$nsid".ora
spf=$ORACLE_HOME/dbs/spfile"$nsid".ora

# keep a copy of ora file under ORACLE_BASE
cp $orafile $ORACLE_BASE/init"$nsid".ora
# start the database for cloning
$Sqlplus / as sysdba @Clone.sql $orafile $spf

if [ $? -ne 0 ]; then
   logerrormsg EM_GENERATE_CONTROLFILE
   exit 1
fi

echo 'SPFILE=$spf' > $initf

$Sqlplus / as sysdba @mountDatabase.sql $CLONE_CRT_SQL

if [ $? -ne 0 ]; then
   logerrormsg EM_CREATE_CONTROLFILE
   exit 1
fi

echo " ********** convert the untiltime to db timezone DB_TZ:$DB_TZ if different  ***************"
if [ ! -z "$DB_TZ" ]; then
untiltime=`$Sqlplus -S / as sysdba @$${scriptsRoot}/oracle/sqlplusShell/TZconvertApp.sql $untiltime $DB_TZ`
echo "untiltime => $untiltime"
retval=$?
if [ "$retval" -gt "0" ]; then
echo "********** error converting the db timezone DB_TZ:$DB_TZ  ***************"
exit 110;
fi
fi

if [ -e $CLONE_CRT_SQL ]; then
   rm -f $CLONE_CRT_SQL
fi

dbrecover="dbrecover_"$nsid".txt"
export ORACLE_SID=$nsid

vnum=0
vnum=`$Sqlplus -S / as sysdba << 'EOF1'
set head off;
set feedback off
select count(*) from v\$instance where version like '12%';
exit;
EOF1`
echo "********************* vnum=$vnum ***********"
pdbcnt=0
if [ "$vnum" -gt "0" ]; then
  pdbcnt=`$Sqlplus -S / as sysdba << 'EOF1'
  set head off;
  set feedback off
  select count(*) from v\$pdbs where name like 'PDB%SEED';
  exit;
EOF1`
fi
export vnum
export pdbcnt

echo "******* recovering database *********** "
echo " recovering database: pdbcnt=$pdbcnt ischild=$ischild " > $dbrecover

#if [ $pdbcnt -gt 0 ]; then
$Sqlplus / as sysdba << EOF
alter database archivelog;
exit;
EOF
#fi

./recoverRman_tstamp.sh $mname $nsid $archmnt $untiltime $dbfsPath
retval=0
recoverdb=true
if [ ! -z "$rrecovery" ] && [ "$rrecovery" = "false" ]; then
   recoverdb=false
fi

if [ "$pdbcnt" -eq "0" ]; then
$Sqlplus / as sysdba @newDatafile.sql
nstmtsql=nstmtsql.sql
echo "set head off;" > $nstmtsql
echo "set feedback off" >> $nstmtsql
echo "select to_char(to_date('$untiltime','yyyy-mm-dd..hh24:mi:ss'),'yyyy-mm-dd..hh24:mi:ss') from dual;" >> $nstmtsql
echo "exit;" >> $nstmtsql
ntstamp=`$Sqlplus -S / as sysdba @$nstmtsql`
echo "ntstamp => $ntstamp"
rm -f $nstmtsql
./recoverRman_tstamp.sh $mname $nsid $archmnt $ntstamp $dbfsPath no
fi

if [ "$pdbcnt" -gt "0" ]; then
$Sqlplus -S / as sysdba << EOF > $offlinefilelist
set head off;
set feed off;
set echo off;
select file# || ':' || ltrim(substr(name,instr(name,'/',-1)),'/')  from v\$datafile where status='RECOVER' and enabled = 'READ WRITE';
exit;
EOF
chmod 755 $offlinefilelist

while read line
do
    set -- $line
    filenum=$1
        echo "filenumber is: $filenum"
if [ "x$filenum" != "x" ]; then
fnm=`echo $filenum | awk -F':' '{print $1}'`
fname=`echo $filenum | awk -F':' '{print $NF}'`
$ORACLE_HOME/bin/rman target / log=$dbrecover append << EOF
run {
SET NEWNAME FOR DATAFILE $fnm TO '$mname/datafile/$fname';
alter database create datafile $fnm as new;
}
exit;
EOF
fi
done < $offlinefilelist
fi

if [ "$pdbcnt" -gt "0" ]; then
$Sqlplus / as sysdba @newDatafile.sql
nstmtsql=nstmtsql.sql
echo "set head off;" > $nstmtsql
echo "set feedback off" >> $nstmtsql
echo "select to_char(to_date('$untiltime','yyyy-mm-dd..hh24:mi:ss'),'yyyy-mm-dd..hh24:mi:ss') from dual;" >> $nstmtsql
echo "exit;" >> $nstmtsql
ntstamp=`$Sqlplus -S / as sysdba @$nstmtsql`
echo "ntstamp => $ntstamp"
rm -f $nstmtsql
./recoverRman_tstamp.sh $mname $nsid $archmnt $ntstamp $dbfsPath no
fi

if [ "$recoverdb" = "true" ]; then
echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openDatabase
echo "alter database open resetlogs;" >> $openDatabase
echo "CREATE temporary TABLESPACE TEMP_CL TEMPFILE '$dataDir/datafile/temp_cl01.dbf' size 5g autoextend on;" >> $openDatabase
echo "exit" >> $openDatabase

echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $openPDatabase
echo "alter database open;" >> $openPDatabase
echo "alter database enable block change tracking;" >> $openPDatabase
echo "CREATE temporary TABLESPACE TEMP_CL TEMPFILE '$ORACLE_BASE/oradata/$nsid/temp_cl01.dbf' size 5g autoextend on maxsize unlimited;" >> $openPDatabase
echo "shutdown immediate;" >> $openPDatabase
echo "startup mount;" >> $openPDatabase
echo "recover database until cancel;" >> $openPDatabase
echo "alter database open resetlogs;" >> $openPDatabase
echo "exit" >> $openPDatabase

openDBlog="openDBlog_"$nsid".txt"

if [ -e $openDBlog ]; then
#   echo "removing $openDBlog before generating a new one"
   rm -f $openDBlog
fi

echo -e "Opening Database is started..." > $openDBlog

if [ "$pdbcnt" -gt "0" ]; then
$Sqlplus / as sysdba @recoverPDBSeed.sql >> $openDBlog
fi


echo "*********************** opendb **************************"
export ORACLE_SID=$nsid
$Sqlplus / as sysdba @$openDatabase >> $openDBlog
retval1=$?
echo "*********************** retval:$retval1 **************************"

orabug=`grep ORA-01092 $openDBlog`
if [ -z "$orabug" ]; then
orabug=`grep ORA-03113 $openDBlog`
fi

if [ ! -z "$orabug" ]; then
sleep 60
$ORACLE_HOME/bin/rman target / log=$dbrecover append << EOF
run {
startup mount;
recover database;
}
exit;
EOF
fi
if [ "$pdbcnt" -gt "0" ]; then
$Sqlplus -S / as sysdba << EOF > $offlinefilelist
set head off;
set feed off;
set echo off;
select file# from v\$datafile where status='OFFLINE';
exit;
EOF
chmod 755 $offlinefilelist

while read line
do
    set -- $line
    filenum=$1
        echo "filenumber is: $filenum"
if [ "x$filenum" != "x" ]; then
$ORACLE_HOME/bin/rman target / log=$dbrecover append << EOF
run {
recover datafile $filenum;
alter database datafile $filenum online;
}
exit;
EOF
fi
done < $offlinefilelist
fi
if [ ! -z "$orabug" ]; then
$Sqlplus / as sysdba @$openPDatabase >> $openDBlog
retval1=$?
fi

if [ "$retval1" -gt "0" ]
then
 while [ "$retval1" -gt "0" ]; do

undfcnt=`$Sqlplus -S / as sysdba << 'EOF1'
set head off;
select count(*) from v\$datafile where name like '%/UNNAMED%';
exit;
EOF1`

  if [ "$undfcnt" -gt "0" ]; then

      undfnum=`$Sqlplus -S / as sysdba << 'EOF2'
        set head off;
        select file# from v\$datafile where name like '%/UNNAMED%';
        exit;
EOF2`

        echo "WHENEVER SQLERROR EXIT SQL.SQLCODE" > $addUnnamed
        echo "alter database create datafile $undfnum as new;" >> $addUnnamed
        echo "exit" >> $addUnnamed

        $Sqlplus / as sysdba @$addUnnamed
       ./recoverRman_tstamp.sh $mname $nsid $archmnt $untiltime $dbfsPath no
        $Sqlplus / as sysdba @$openDatabase >> $openDBlog
        retval1=$?;
        orabug=`grep ORA-01092 $openDBlog`
        if [ -z "$orabug" ]; then
                orabug=`grep ORA-03113 $openDBlog`
        fi
        if [ ! -z "$orabug" ]; then
                echo "******************" >> $openDBlog
                echo "ACT_PDB_TF" >> $openDBlog
$ORACLE_HOME/bin/rman target / log=$dbrecover append << EOF
run {
startup mount;
recover database;
}
exit;
EOF
                $Sqlplus / as sysdba @$openPDatabase >> $openDBlog
                retval1=$?
                oraissue=`awk '/ACT_PDB_TF/{y=1;next}y' $openDBlog | grep ORA-01196`
                if [ ! -z "$oraissue" ]; then
                   logerrormsg EM_DB_OPEN "$oraissue"
                   exit 1
                   #     exit $oraissue
                fi
         fi
   else
     retval1=0
   fi
retval1=$?;
 done
fi

ggusername=`$Sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none;
select distinct owner from dba_segments where segment_name like 'GGS_%' and owner<>'SYS' and owner<>'SYSTEM' and rownum<2;
exit;
EOF1`

if [ ! -z "$ggusername" ]; then
echo "Golden Gate User:$ggusername"
$Sqlplus -S / as sysdba <<EOF1
WHENEVER SQLERROR EXIT SQL.SQLCODE
alter user "$ggusername" account lock;
startup force restrict;
drop user "$ggusername" cascade;
exit;
EOF1
retval=$?
echo "Drop User: $retval"

if [ "$retval" -gt "0" ]; then
$Sqlplus -S / as sysdba <<EOF1
WHENEVER SQLERROR EXIT SQL.SQLCODE
shutdown abort;
startup force restrict;
drop user "$ggusername" cascade;
exit;
EOF1
fi
fi

opencnt=`$Sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?
if [ "$retval" -gt "0" ]; then
$Sqlplus / as sysdba << EOF
startup force restrict;
exit;
EOF
fi

if [ "$pdbcnt" -gt "0" ]; then
$Sqlplus -S / as sysdba << EOF > $pluggablelist
set head off;
set feed off;
set echo off;
select name from v\$pdbs where con_id in (select con_id from v\$recover_file where error = 'UNKNOWN ERROR');
exit;
EOF
chmod 755 $pluggablelist

while read line
do
    set -- $line
    filenum=$1
        echo "filenumber is: $filenum"
if [ "x$filenum" != "x" ]; then
$ORACLE_HOME/bin/rman target / log=$dbrecover append << EOF
run {
recover pluggable database $filenum;
}
exit;
EOF
fi
done < $pluggablelist

$ORACLE_HOME/bin/rman target / log=$dbrecover append << 'EOF'
run {
alter session set "_oracle_script"=true;
ALTER pluggable database all open;
alter pluggable database all save state;
}
exit;
EOF
fi

opencnt=`$Sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?

if [ "$retval" -gt "0" ]; then
 oraerror=`grep ORA-01092 $openDBlog`
 logerrormsg EM_DB_READ_WRITE "$oraerror"
 exit 1;
elif [ "$opencnt" -eq "0" ]; then
 oraerror=`grep ORA-01092 $openDBlog`
 logerrormsg EM_DB_READ_WRITE "$oraerror"
 exit 1;
fi

tempg=0
tempg=`$Sqlplus -S / as sysdba << 'EOF1'
set head off;
set feedback off
select count(*) from dba_tablespace_groups;
exit;
EOF1`

if [ "$tempg" -gt "0" ]; then
tempgn=`$Sqlplus -S / as sysdba << 'EOF1'
set head off;
set feed off;
set echo off;
select distinct group_name from dba_tablespace_groups where group_name in (select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE');
exit;
EOF1`
fi

tabgroup="tabgroup_"$nsid".txt"

if [ "x$tempgn" != "x" ]; then
$Sqlplus -S / as sysdba << 'EOF1' > $tabgroup
set head off;
set feed off;
set echo off;
select tablespace_name from dba_tablespace_groups where group_name in (select distinct group_name from dba_tablespace_groups where group_name in (select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'));
exit;
EOF1
chmod 755 $tabgroup
else
tempn=`$Sqlplus -S / as sysdba << 'EOF1'
set head off;
select property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
exit;
EOF1`
fi

if [ "$pdbcnt" -eq "0" ]; then
$Sqlplus -S / as sysdba << EOF > $tabfilelist
set head off;
set feed off;
set echo off;
select tb.name tabname from v\$tablespace tb where tb.name != 'TEMP_CL' and ts# not in (select ts# from v\$datafile);
exit;
EOF
else
$Sqlplus -S / as sysdba << EOF > $tabfilelist
set head off;
set feed off;
set echo off;
select tb.name tabname from v\$tablespace tb where tb.name != 'TEMP_CL' and con_id not in (select con_id from v\$pdbs where name != 'PDB\%SEED') and ts# not in (select ts# from v\$datafile where con_id not in (select con_id from v\$pdbs where name != 'PDB\%SEED'));
exit;
EOF
fi

echo "alter database default temporary tablespace TEMP_CL;" > $openDatabase1

while read line
do
    set -- $line
    tabname=$1
        echo "outside if $tabname"
        if [ "x$tabname" != "x" ]; then
        echo "inside if $tabname"
        tmpstr1="$tabname""_01.dbf"
        tmpstr=" '$dataDir/datafile/$tmpstr1' size 5g autoextend on;"
        echo "drop tablespace $tabname including contents and datafiles;" >> $openDatabase1
        echo "create temporary tablespace $tabname tempfile $tmpstr" >> $openDatabase1
        fi
done < $tabfilelist

echo "shutdown immediate;" >> $openDatabase1
echo  "startup;" >> $openDatabase1

if [ "x$tempgn" != "x" ]; then
while read line
do
    set -- $line
    tabgname=$1
        echo "tab group if $tabgname"
        if [ "x$tabgname" != "x" ]; then
        echo "in tab group if $tabgname"
        echo "alter tablespace $tabgname tablespace group $tempgn;" >> $openDatabase1
        fi
done < $tabgroup

echo "alter database default temporary tablespace $tempgn;" >> $openDatabase1
else
echo "alter database default temporary tablespace $tempn;" >> $openDatabase1
fi

echo  "exit" >> $openDatabase1
runnidsql="runnidrotbs"_$nsid".sql"

if [ "$nonid" != "true" ]; then
if [ ! -f "$dbfsPath" ]; then
   roTBS='NONE'
else
   roTBS=`cat $dbfsPath`
fi

roCTBS=''
for i in $(echo $roTBS | tr "," "\n")
do
    echo "$i"
    if [ -z "$roCTBS" ]; then
	roCTBS="'''"${i}"'''"
    else
	roCTBS=${roCTBS}"\n'''"${i}"'''"
    fi
done

echo "$roCTBS"

ctbs_input=''
echo "$roCTBS" > roCTBS_output.txt
ctbs_input=`cat roCTBS_output.txt`
echo "$ctbs_input"

runnidsql="runnidrotbs"_$nsid".sql"
if [ -e "$runnidsql" ]; then
rm -f $runnidsql
fi

while read line
do
    set -- $line
    readtabname=$1
        if [ "x$readtabname" != "x" ]; then
        echo "@nidROTBS.sql $readtabname ONLINE" >> $runnidsql
        echo "@nidROTBS.sql $readtabname 'READ WRITE'" >> $runnidsql
        fi
done < roCTBS_output.txt
chmod 755 $runnidsql

$Sqlplus / as sysdba << EOF
@$runnidsql
shutdown immediate;
startup mount;
exit;
EOF
change_dbid $nsid $ORACLE_HOME
if [ ! -f "$runnidsql" ]; then
 rm -f $runnidsql
fi

while read line
do
    set -- $line
    readtabname=$1
        if [ "x$readtabname" != "x" ]; then
        echo "@nidROTBS.sql $readtabname 'READ ONLY'" >> $runnidsql
        fi
done < roCTBS_output.txt
chmod 755 $runnidsql

$Sqlplus / as sysdba << EOF
startup mount;
alter database open resetlogs;
@$runnidsql
exit;
EOF
fi

if [ $? -ne 0 ]; then
   logerrormsg EM_NID
   exit 1
fi

if [ ! -f "roCTBS_output.txt" ]; then
 rm -f roCTBS_output.txt
fi

export ORACLE_SID=$nsid
$Sqlplus / as sysdba @$openDatabase1 >> $openDBlog
retval=$?

if [ -e $dbrecover ]; then
   echo "removing $dbrecover "
#   rm -f $dbrecover
fi

if [ -e $openDatabase ]; then
   echo "removing $openDatabase "
   rm -f $openDatabase
fi
if [ -e $verifyDatabase ]; then
   echo "removing $verifyDatabase before generating a new one"
   rm -f $verifyDatabase
fi
if [ -e $openDatabase1 ]; then
   echo "removing $openDatabase1 "
   rm -f $openDatabase1
fi
if [ -e $addUnnamed ]; then
   echo "removing $addUnnamed "
   rm -f $addUnnamed
fi

if [ -e $openDBlog ]; then
   echo "removing $openDBlog "
 #  rm -f $openDBlog
fi

if [ -e $tabfilelist ]; then
   echo "removing $tabfilelist "
   rm -f $tabfilelist
fi

if [ -e $offlinefilelist ]; then
   echo "removing $offlinefilelist "
   #rm -f $offlinefilelist
fi

if [ -e $pluggablelist ]; then
   echo "removing $pluggablelist "
   #rm -f $pluggablelist
fi

if [ -e $createRedolog ]; then
  # echo "removing $createRedolog "
   rm -f $createRedolog
fi

opencnt=`$Sqlplus -S / as sysdba << 'EOF1'
WHENEVER SQLERROR EXIT SQL.SQLCODE
set head off;
set feedback off
set newpage none
select count(*) from v\$database where open_mode = 'READ WRITE';
exit;
EOF1`
retval=$?

if [ "$opencnt" -eq "0" ]; then
   logerrormsg EM_DB_READ_WRITE
   exit 1;
fi

export ORACLE_SID=$nsid
$Sqlplus / as sysdba  << EOF3
drop tablespace TEMP_CL including contents and datafiles;
exit;
EOF3

if [ "$pdbcnt" -gt "0" ]; then
$Sqlplus / as sysdba << EOF
alter session set "_oracle_script"=true;
ALTER pluggable database all open instances=all;
alter pluggable database all save state instances=all;
exit;
EOF
fi

if [ "$pdbcnt" -gt "0" ]; then
pdbname=`$Sqlplus -S / as sysdba << 'EOF1'
set head off;
set feed off;
set echo off;
set linesize 4000;
set newpage none;
select ltrim(rtrim(listagg(to_char(name),',') WITHIN GROUP (ORDER BY name))) from v\$pdbs where name not like 'PDB%SEED';
exit;
EOF1`

for x in $(echo $pdbname | tr "," "\n")
do
mkdir -p $ORACLE_BASE/oradata/$nsid/$x
$Sqlplus / as sysdba << EOF
@tmpPDB.sql "$x" "$ORACLE_BASE/oradata/$nsid/$x"
EOF
done
fi

echo "set pagesize 100" >> $verifyDatabase
echo "set linesize 100" >> $verifyDatabase
echo "set echo off" >> $verifyDatabase
echo "set feedback off" >> $verifyDatabase
echo "set wrap on" >> $verifyDatabase
echo "col tablespace_name format a15" >> $verifyDatabase
echo "col file_name format a75" >> $verifyDatabase
echo "select distinct tablespace_name, file_name from dba_data_files order by 1;" >> $verifyDatabase
echo "select owner,count(*) from dba_tables group by owner order by 1;" >> $verifyDatabase
echo "select dbid, name, open_mode from v\$database;" >> $verifyDatabase
echo "select sum(bytes)/(1024*1024*1024) \"dbSize (GB)\" from dba_data_files;" >> $verifyDatabase
echo "select * from v\$instance;" >> $verifyDatabase
echo "exit" >> $verifyDatabase
fi

setdb="setdb_"$nsid
echo "# Modify the following variables based on the environment" > $setdb
echo "export ORACLE_SID=$nsid" >> $setdb
echo "export ORACLE_HOME=$ORACLE_HOME" >> $setdb
echo " " >> $setdb
echo "export PATH=$ORACLE_HOME/bin:$PATH" >> $setdb
echo "#END OF FILE" >> $setdb

cp $setdb $dataDir

export ORACLE_SID=$nsid
if [ "$retval" -gt "0" ]; then
echo "********** error cloning database ***************"
#exit 1;
fi

if [ -z "$hostip" ]; then
   hostip="localhost"
fi

# update tnsnames.ora
#./updateTNSNames.sh $nsid $ORACLE_HOME $hostip
export ORACLE_SID=$nsid
vnum=0
vnum=`sqlplus -S / as sysdba << 'EOF1'
set head off;
set feedback off
select count(*) from v\$instance where version like '10%';
exit;
EOF1`

set +x
syspwd="change_on_install"
if [ ! -z "$ouser" ] && [ "$ouser" = "sys" ] && [ ! -z "$opass" ]; then
   syspwd=$opass
fi

export ORACLE_SID=$nsid
# create pwfile
if [ "$vnum" -gt "0" ]; then
#orapwd file=$ORACLE_HOME/dbs/orapw$nsid password=change_on_install ignorecase=yes
  orapwd file=$ORACLE_HOME/dbs/orapw$nsid password=$syspwd entries=5
else
  orapwd file=$ORACLE_HOME/dbs/orapw$nsid password=$syspwd ignorecase=yes
fi

# update /etc/oratab for reprotect
oratabentry=$nsid":"$oracleHome":Y"
echo "$oratabentry" >> /etc/oratab
fi

if [ "$recoverdb" = "true" ]; then
# grant user sysdba privilege
if [ ! -z "$ouser" ] && [ "$ouser" != "sys" ]; then
$Sqlplus / as sysdba << EOF
grant sysdba to $ouser;
exit;
EOF
fi

cp $ORACLE_HOME/dbs/orapw$nsid $dataDir

$Sqlplus / as sysdba << EOF
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
exit;
EOF
fi
ORACLE_SID=$nsid
export ORACLE_SID
$Sqlplus / as sysdba @verifyDatabase.sql

retval=$?
if [ -e $verifyDatabase ]; then
#   echo "removing $verifyDatabase before generating a new one"
   rm -f $verifyDatabase
fi
if [ "$retval" -gt "0" ]; then
echo "********** error cloning database ***************"
exit 1;
fi

exit 0
